package com.assignment.database;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.imageio.ImageIO;
import javax.swing.JOptionPane;

import com.assignment.main.Item;
import com.assignment.main.Supplier;

public class DatabaseExtension extends Database {

	public static boolean checkUserCredentials(String name, String pass) {

		Database.connect();

		String query = String.format(
				"select * from Users where username='%s' and password='%s';",
				name, pass);

		String[][] result = Database.returnQuery(query);
		Database.close();

		if (result[0].length > 0) {
			// Database.printQuery(result);
			return true;
		}
		return false;

	}

	// Retreive Items from the database
	public static Item[] getItems(int type) {
		Database.connect();

		String[][] result = returnQueryNoImage(String.format(
				"select * from Item where Item_Type='%d';", type));
		printQuery(result);
		Item items[] = new Item[result[0].length];
		String data[] = new String[result.length];

		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				// if (x==8)
				// continue;
				data[x] = result[x][y];
				// System.out.printf("Data %d %d: %s\n", x,y,result[x][y]);
			}
			items[y] = new Item(data);
			items[y].setType(type);
			try {
				items[y].setImage(getImage(items[y].getId()));
			} catch (Exception e) {
			}
			;
		}
		Database.close();
		return items;
	}

	// Retreive Items from the database
	public static Supplier[] getSuppliers() {
		Database.connect();

		String[][] result = returnQuery(String
				.format("select * from Supplier;"));
		Supplier suppliers[] = new Supplier[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			suppliers[y] = new Supplier(data);
		}
		Database.close();
		return suppliers;
	}

	public static boolean saveItem(Item item, boolean isNew) {
		Database.connect();
		try {
			if (isNew) {
				String query = String
						.format("insert into item values(null,'%s','%s','%d','%d','%.2f','%.2f','%s',null,'%s');",
								item.getName(), item.getDescription(),
								item.getType(), item.getStock(),
								item.getBuyPrice(), item.getSellPrice(),
								item.getStyle(), item.getSupplierId());
				Database.doSQL(query);
				Database.insertImage(
						"" + (getItems(item.getType()).length - 1),
						item.getFile());
			} else {
				System.out.println("style: " + item.getStyle() + "supplier: "
						+ item.getSupplierId());
				String query = String
						.format("update item set item_name='%s',item_description='%s',item_type='%d',Stock_Quantity='%d'"
								+ ",buy_price='%.2f',sell_price='%.2f',style='%d',supplierid='%s' where item.pk_item_id='%s';",
								item.getName(), item.getDescription(),
								item.getType(), item.getStock(),
								item.getBuyPrice(), item.getSellPrice(),
								item.getStyle(), item.getSupplierId(),
								item.getId());
				Database.doSQL(query);

				if (item.getFile() != null)
					Database.insertImage(item.getId(), item.getFile());

			}

		} catch (Exception e) {
			e.printStackTrace();
			Database.close();
			return false;
		}
		Database.close();
		return true;
	}

	public static boolean saveSupplier(Supplier supplier, boolean isNew) {
		Database.connect();
		try {
			if (isNew) {
				String query = String.format(
						"insert into supplier values (null,'%s','%s','%s');",
						supplier.getName(), supplier.getCollection(),
						supplier.getAddress());
				Database.doSQL(query);

			} else {
				String query = String
						.format("update Supplier set supplier_name='%s',collection='%s',address='%s' where supplier.pk_supplier_id='%s';",
								supplier.getName(), supplier.getCollection(),
								supplier.getAddress(), supplier.getId());
				Database.doSQL(query);

			}
			Database.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			Database.close();
			return false;
		}
	}

	public static boolean deleteItem(Item item) {

		Database.connect();
		String query = String.format("delete from item where pk_item_id='%s';",
				item.getId());

		Database.doSQL(query);
		Database.close();
		return true;

	}

	public static boolean deleteSupplier(Supplier sup) {
		Database.connect();
		String query = String.format(
				"delete from supplier where pk_supplier_id='%s';", sup.getId());

		if (Database.doSQL(query)) {
			Database.close();
			return true;
		}

		else {
			JOptionPane
					.showMessageDialog(
							null,
							"Unable to delete Supplier, please ensure no Items in database are from this Supplier");
			Database.close();
			return false;

		}
	}

}
